Deleting Data

Deleting Data#

We can delete rows from a table using the DELETE statement. A delete statement deletes an entire row and not individual columns. If changing a particular column value for a row is desired, use the UPDATE statement, which we will cover next. Also realize that deleting all the rows of a table doesn’t delete the table itself.

Example Syntax#

DELETE FROM table

WHERE col3 > 5

ORDER BY col1

LIMIT 5;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/13lesson.sh and wait for the MySQL prompt to start-up.

1. We’ll start with deleting just one row. In order to target a single row, we’ll need to use the WHERE clause similar to how we used it with a select statement. Let’s delete the row for the actress priyanka.

   DELETE FROM Actors WHERE FirstName="priyanka";
   

Note that the string comparison for the FirstName performed by MySQL doesn’t take case into account and if we specified the WHERE clause with an uppercase as FirstName=“PRIYANKA” the row would still be deleted.

2. The delete statement will delete all the matching rows, which in the previous example is only one. As an example, we can write a query to delete all male actors as follows:

   DELETE FROM Actors WHERE Gender="Male";
   

3. Next, suppose that out of the remaining female actors in our database, we want to delete the top three actresses by net worth. We can accomplish that by using the ORDER BY and LIMIT clauses.

   DELETE FROM Actors ORDER BY NetWorthInMillions DESC LIMIT 3;
   

The above query removes the top three actresses by net worth.

4. We can also remove all the rows from a table using the following query:

   DELETE FROM Actors;
   

Notice that the table can still be queried even after we have removed all the rows from it.